﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Tool.UpdateIT;
using UpdateITUtilities;
using UpdateITChangeSets;
using UpdateITClient;
namespace Tool
{

    public partial class Form1 : Form
    {
        public static string CpiamConnectionString = "Data Source=.\\CPIAM;Initial Catalog=CPIAMPOSMaxRetail;Integrated Security=True";
        private List<SupplierCodesMaster> lstSupplierCodes = new List<SupplierCodesMaster>();

        public class CheckedListBoxItem
        {
            public string Text { get; set; }
            public object Value { get; set; }

            public override string ToString()
            {
                return Text;
            }
        }

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (!TestSetting("System_StockUpdatesState"))
                InsertSetting("System_StockUpdatesState","");
            if (!TestSetting("System_StockUpdatesPrimarySupplier"))
                InsertSetting("System_StockUpdatesPrimarySupplier", "");
            if (!TestSetting("System_StockUpdatesSecondarySuppliers"))
                InsertSetting("System_StockUpdatesSecondarySuppliers", "");
            if (!TestSetting("System_StockUpdatesChain"))
                InsertSetting("System_StockUpdatesChain", "");

            if (!TestSetting("Pricing_GSTRate"))
                InsertSetting("Pricing_GSTRate", "True");
            if (!TestSetting("Pricing_RoundTo5Cents"))
                InsertSetting("Pricing_RoundTo5Cents", "True");
            if (!TestSetting("System_StockUpdatesSupplierDetails"))
                InsertSetting("System_StockUpdatesSupplierDetails", "True");
            if (!TestSetting("System_StockUpdatesStockName"))
                InsertSetting("System_StockUpdatesStockName", "True");
            if (!TestSetting("System_StockUpdatesGSTFlag"))
                InsertSetting("System_StockUpdatesGSTFlag", "True");
            if (!TestSetting("System_StockUpdatesDeptDetails"))
                InsertSetting("System_StockUpdatesDeptDetails", "True");
            if (!TestSetting("System_StockUpdatesPackSize"))
                InsertSetting("System_StockUpdatesPackSize", "True");
            if (!TestSetting("System_StockUpdatesEthicalFlag"))
                InsertSetting("System_StockUpdatesEthicalFlag", "True");

            if (TestSuppliers())
            {
                PopulateSupplierDic();
                foreach (var item in SupplierDic)
                {
                    checkedListBox1.Items.Add(item.Key);
                }
            }
            else
            {
                LoadSuppliers();
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == 0)
            {
                comboBox2.Items.Clear();
                comboBox2.Items.Add("GN");
                comboBox2.Items.Add("CW");
                comboBox2.Items.Add("IPA");
                comboBox2.Items.Add("PA");
                comboBox2.Items.Add("SP");
            }
            else if (comboBox1.SelectedIndex == 1)
            {
                comboBox2.Items.Clear();
                comboBox2.Items.Add("GN");
                comboBox2.Items.Add("AMC");
                comboBox2.Items.Add("GRD");
            }
            else
            {
                comboBox2.Items.Clear();
                comboBox2.Items.Add("ALL");
            }
        }

        public static Dictionary<string, string> SupplierDic;
        public void PopulateSupplierDic()
        {
            SupplierDic = new Dictionary<string, string>();
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"select SupplierName, SupXRef from Suppliers where suppliername<>'API PTY LTD' and suppliername<>'SYMBION PTY LTD' and suppliername<>'SIGMA PTY LTD' and suppliername<>'NOT SET'");
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string SupplierName = reader.GetString(0);
                    string SupplierCode = reader.IsDBNull(1) ? null : reader.GetString(1);
                    SupplierDic[SupplierName] = SupplierCode;
                }
                reader.Close();
            }
            dbConn.Close();
        }

        private void LoadSuppliers()
        {
            Byte[] bytRslt = QuickLZ.decompress(GetSupplierCodes());
            ChangeSet chnSet = SilverlightSerializer.Deserialize(bytRslt) as ChangeSet;
            lstSupplierCodes = chnSet.SupplierCodesMasters;
            foreach (SupplierCodesMaster suppchange in lstSupplierCodes)
            {
                InsertSuppliers(suppchange.SupplierNo, suppchange.SupplierID, suppchange.SupplierName.ToUpper(), suppchange.SupplierCode.ToUpper());
                InsertSuppliersCode(suppchange.SupplierCode.ToUpper());
            }
            PopulateSupplierDic();
            checkedListBox1.Items.Clear();
            foreach (var item in SupplierDic)
            {
                checkedListBox1.Items.Add(item.Key);
            }
        }

        public Byte[] GetSupplierCodes()
        {
            UpdateTablesServiceClient client = new UpdateTablesServiceClient();
            return client.GetTableChanges(Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Day, 1, Microsoft.VisualBasic.DateAndTime.Today), "AAA", "AAA", new string[] { "AAA" }, "AAA", 10081, "116");
        }

        public void InsertSuppliers(Guid SuppNo, int SupID, string SuppName, string XRef)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"SET IDENTITY_INSERT Suppliers ON
                            INSERT INTO Suppliers
                         (SupplierNo, SupplierID, SupplierName, SupAddress, SupSuburb, SupState, SupPostCode, SupXRef, SupAccNo, SupAccPass, SupAccNoTwo, SupAccPassTwo, 
                         PharmXActive, PharmXSuppID, PharmXOrderCode, PharmXOrderMethod, PharmXOrderType, PharmXOrderBUP, PharmXOrderGroup, SupIsSystem)
                         VALUES(@SuppNo,@SupID,@SuppName, 'Address Not Set', 'SUBURB NOT SET', 'N/A', '0000', @XRef, '000000', 'N/A', '000000', 'N/A', 0, 0, 'N/A', 'N/A', 'N/A', 'N/A', 'N/A', 1)
                         SET IDENTITY_INSERT Suppliers OFF");
                command.Parameters.AddWithValue("@SuppNo", SuppNo);
                command.Parameters.AddWithValue("@SupID", SupID);
                command.Parameters.AddWithValue("@SuppName", SuppName);
                command.Parameters.AddWithValue("@XRef", XRef);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void InsertSuppliersCode(string SupplierCode)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"INSERT INTO SupplierCodes (SupplierCode) VALUES (@SupplierCode)");
                command.Parameters.AddWithValue("@SupplierCode", SupplierCode);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void InsertSetting(string SettingName, String SettingValue)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"INSERT INTO tblSettings (SettingName, SettingValue) VALUES (@SettingName, @SettingValue)");
                command.Parameters.AddWithValue("@SettingName", SettingName);
                command.Parameters.AddWithValue("@SettingValue", SettingValue);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public Boolean TestSetting(string SettingName)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"select * from tblSettings where SettingName=@SettingName");
                command.Parameters.AddWithValue("@SettingName", SettingName);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
            }
            dbConn.Close();
            return false;
        }

        public Boolean TestSuppliers()
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"select * from Suppliers");
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
            }
            dbConn.Close();
            return false;
        }

        public void UpdatesPrimarySupplier(string PrimarySupplier)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"UPDATE tblSettings set SettingValue=@PrimarySupplier where SettingName='System_StockUpdatesPrimarySupplier'");
                command.Parameters.AddWithValue("@PrimarySupplier", PrimarySupplier);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void UpdatesSecondarySupplier(string SecondarySupplier)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"UPDATE tblSettings set SettingValue=@SecondarySupplier where SettingName='System_StockUpdatesSecondarySuppliers'");
                command.Parameters.AddWithValue("@SecondarySupplier", SecondarySupplier);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void UpdatesState(string State)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"UPDATE tblSettings set SettingValue=@State where SettingName='System_StockUpdatesState'");
                command.Parameters.AddWithValue("@State", State);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void UpdatesChain(string Chain)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"UPDATE tblSettings set SettingValue=@Chain where SettingName='System_StockUpdatesChain'");
                command.Parameters.AddWithValue("@Chain", Chain);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        public void UpdateDatabase(string sql)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(sql);
                command.ExecuteNonQuery();
            }
            dbConn.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string PrimarySupplier=comboBox1.SelectedItem==null? "None" : comboBox1.SelectedItem.ToString();
            string Chain = comboBox2.SelectedItem == null ? "None" : comboBox2.SelectedItem.ToString();
            string State = comboBox2.SelectedItem == null ? "None" : comboBox3.SelectedItem.ToString();
            string SecondarySupplier = "";
            if (checkedListBox1.CheckedItems.Count != 0)
            {
                for (int x = 0; x <= checkedListBox1.CheckedItems.Count - 1; x++)
                {
                    SecondarySupplier = SecondarySupplier + SupplierDic[checkedListBox1.CheckedItems[x].ToString()];
                    if (x < checkedListBox1.CheckedItems.Count - 1)
                        SecondarySupplier += ",";
                }
            }
            UpdatesPrimarySupplier(PrimarySupplier);
            UpdatesSecondarySupplier(SecondarySupplier);
            UpdatesChain(Chain);
            UpdatesState(State);
            label5.Text = "Setting Finished";
            MessageBox.Show("Setting Finished");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            List<ManfFileMaster> lstManf = new List<ManfFileMaster>();
            List<PBSManufacturerMaster> lstPBSManf = new List<PBSManufacturerMaster>();
            List<DeptFileMaster> lstDept = new List<DeptFileMaster>();
            List<SupplierCodesMaster> lstSupplierCodes = new List<SupplierCodesMaster>();
            List<StockFileApnsMaster> lstStockAPN = new List<StockFileApnsMaster>();
            List<StockFilePDEMaster> lstStockPDE = new List<StockFilePDEMaster>();
            List<StockFileMaster> lstStockMaster = new List<StockFileMaster>();
            List< StockFileDetailsMaster> lstStockFileDetails = new List<StockFileDetailsMaster>();
            List<TableChange> lstDispenseITDetails = new List<TableChange>();
            List<MergedDuplicate> lstMergedDups = new List<MergedDuplicate>();
            ChangeSet updateITChangeSet=null;
            string PrimarySupplier=comboBox1.SelectedItem==null? "None" : comboBox1.SelectedItem.ToString();
            string Chain = comboBox2.SelectedItem == null ? "None" : comboBox2.SelectedItem.ToString();
            string State = comboBox2.SelectedItem == null ? "None" : comboBox3.SelectedItem.ToString();
            string SecondarySupplier = "";
            if (checkedListBox1.CheckedItems.Count != 0)
            {
                for (int x = 0; x <= checkedListBox1.CheckedItems.Count - 1; x++)
                {
                    SecondarySupplier = SecondarySupplier + SupplierDic[checkedListBox1.CheckedItems[x].ToString()];
                    if (x < checkedListBox1.CheckedItems.Count - 1)
                        SecondarySupplier += ",";
                }
            }
            UpdatesPrimarySupplier(PrimarySupplier);
            UpdatesSecondarySupplier(SecondarySupplier);
            UpdatesChain(Chain);
            UpdatesState(State);
            string[] strSuppliers;
            if (SecondarySupplier.Length > 0)
            {
                strSuppliers = (PrimarySupplier + "," + SecondarySupplier).Split(',');
            }
            else
            {
                strSuppliers = new String[] { PrimarySupplier };
            }
            label5.Text = "Downloading data...";
            UpdateTablesServiceClient client = new UpdateTablesServiceClient();
            Byte[] compressedData = client.GetTableChanges(null, State, Chain, strSuppliers, PrimarySupplier, 10081, "116");
            label5.Text = "Processing data...";
            Byte[] bytRslt = QuickLZ.decompress(compressedData);
            if (bytRslt != null)
            {
                ChangeSet chnSet = SilverlightSerializer.Deserialize(bytRslt) as ChangeSet;
                if (chnSet != null)
                {
                    updateITChangeSet = chnSet;
                    lstManf = chnSet.ManfFileMasterChanges;
                    lstPBSManf = chnSet.PBSManufacturersMasterChanges;
                    lstDept = chnSet.DeptFileMasterChanges;
                    lstStockAPN = chnSet.StockFileApnsMasterChanges;
                    lstStockPDE = chnSet.StockFilePDEMasterChanges;
                    lstStockMaster = chnSet.StockFileMasterChanges;
                    lstStockFileDetails = chnSet.StockFileDetailsMasterChanges;
                    lstDispenseITDetails = chnSet.TableChanges;
                    lstMergedDups = chnSet.MergedDuplicates;
                    lstSupplierCodes = chnSet.SupplierCodesMasters;
                }
            }
            label5.Text = "Inserting to database...";
            UpdateITLogger updateLogger = new UpdateITLogger();
            if(updateITChangeSet!=null){
                ChangeSetsProcessor.GetInstance().ProcessChanges(updateITChangeSet, CpiamConnectionString, updateLogger);
                ChangeSetsProcessor.GetInstance().ProcessDispense(updateITChangeSet, CpiamConnectionString, updateLogger);
            }
            Cleanup();
            label5.Text = "Database generating Finished";
            MessageBox.Show("Database generating Finished");
        }

        public void Cleanup()
        {
            UpdateDatabase("UPDATE StockFilePDES SET StkDefaultSupplier = 0 FROM StockFilePDES INNER JOIN StockFile ON StockFilePDES.StkID = StockFile.StkID AND StockFilePDES.StkSupplierID <> StockFile.SupplierID WHERE (StockFilePDES.StkDefaultSupplier = 1)");
            UpdateDatabase("UPDATE StockFile SET SupplierID = StockFilePDES.StkSupplierID FROM StockFile INNER JOIN StockFilePDES ON StockFile.StkID = StockFilePDES.StkID WHERE (StockFile.SupplierID = 0)");
            UpdateDatabase("UPDATE StockFile SET DeptNo = 9999 WHERE (DeptNo = 0)");
            UpdateDatabase("UPDATE StockFilePDES SET StkDefaultSupplier = 1 FROM StockFilePDES INNER JOIN StockFile ON StockFilePDES.StkID = StockFile.StkID AND StockFilePDES.StkSupplierID = StockFile.SupplierID WHERE (StockFilePDES.StkDefaultSupplier <> 1) AND (StockFilePDES.IsSystem = 1)");
            UpdateDatabase("UPDATE StockFile SET StkExternalDispPde = StockFilePDES.StkPDE FROM StockFile INNER JOIN StockFilePDES ON StockFile.StkID = StockFilePDES.StkID AND StockFile.StkExternalDispPde <> StockFilePDES.StkPDE WHERE (StockFilePDES.StkDefaultSupplier = 1)");
            UpdateDatabase("UPDATE StockFilePDES set StkDefaultSupplier = 0 where StkPDEID in (select StkPDEID from (select StkID, StkPDEID, ROW_NUMBER() OVER (partition by StkID order by StkDefaultSupplier)  AS 'Row_Num' from stockfilepdes where stkid in (select StkID from stockfilepdes where StkDefaultSupplier = 1 group by stkid having COUNT(StkID) > 1) and StkDefaultSupplier = 1) as MultipleStkIDs where Row_Num > 1)");
            UpdateDatabase("UPDATE StockFilePDES SET StkSupplierDesc = StockFile.StkName FROM StockFilePDES INNER JOIN StockFile ON StockFilePDES.StkID = StockFile.StkID WHERE (StockFilePDES.StkNumber > 0) AND (StockFilePDES.StkSupplierDesc = N'No Supplier Description')");
            UpdateDatabase("UPDATE StockFile SET StkPackSize = 1 WHERE (StkPackSize = 0)");
        }
    }
}
